from .mgr import DBMgr

def parseQueryCondition(condition):
    con = 'where '
    for key in condition:
        val = condition[key]
        con += ('%s="%s" and ' if isinstance(val, str) else '%s=%s and ') % (key, val)

    return con[0: -5]

def select(tableName, columns='*', condition=None):
    with DBMgr.getConnect() as conn:
        cursor = None
        limit = None
        try:
            if condition and 'page' in condition:
                page = int(condition.pop('page', 0))
                length =  int(condition.pop('length', 10))
                limit = ' limit %s, %s' % ((page - 1) * length, length)
            
            sqlFilter = parseQueryCondition(condition) if condition else ''
            cursor = conn.cursor()
            
            total = 0
            if limit:
                sqlTotal = 'select count(1) as total from %s %s' % (tableName, sqlFilter)
                cursor.execute(sqlTotal)
                total = cursor.fetchone()[0]
                sqlFilter += limit
            
            sql = 'select %s from %s %s' % (columns, tableName, sqlFilter)
            cursor.execute(sql)
            columns = tuple(x for x in list(zip(*cursor.description))[0])
            data = [dict(zip(columns, x)) for x in cursor.fetchall()]
            if total == 0: total = cursor.rowcount
            return (True, total, data)
        except Exception as e:
            return (False, 0, str(e))
        finally:
            if cursor:
                cursor.close()

def selectCount(tableName, condition=None):
    with DBMgr.getConnect() as conn:
        cursor = None
        try:
            sqlFilter = parseQueryCondition(condition) if condition else ''
            cursor = conn.cursor()
            
            total = 0
            sqlTotal = 'select count(1) as total from %s %s' % (tableName, sqlFilter)
            cursor.execute(sqlTotal)
            total = cursor.fetchone()[0]
             
            return (True, total)
        except Exception as e:
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def insert(tableName, kwargs):
    with DBMgr.getConnect() as conn:
        try:
            sql = 'insert into %s (%s) values(%s)' % (tableName, ','.join(kwargs.keys()), ','.join(['%s' for v in kwargs.values()]))
            cursor = conn.cursor()
            cursor.execute(sql, list(kwargs.values()))
            conn.commit()
            return (True, cursor.lastrowid)
        except Exception as e:
            conn.rollback()
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def insertMany(tableName, kwargs):
    cols = kwargs[0].keys()
    with DBMgr.getConnect() as conn:
        try:
            sql = 'insert into %s(%s) values(%s)' % (tableName, ','.join(cols), ','.join(['%s' for k in cols]))
            cursor = conn.cursor()
            cursor.executemany(sql, [tuple(v.values()) for v in kwargs])
            conn.commit()
            return (True, '')
        except Exception as e:
            conn.rollback()
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def delMany(tableName, kwargs):
    with DBMgr.getConnect() as conn:
        try:
            sql = 'delete from %s where %s' % (tableName, kwargs['filter'])
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            return (True, '')
        except Exception as e:
            conn.rollback()
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def update(tableName, condition, kwargs):
    with DBMgr.getConnect() as conn:
        try:
            sqlFilter = parseQueryCondition(condition)
            sql = 'update %s set %s %s' % (tableName, ','.join(['%s=%%s' % k for k in kwargs.keys()]), sqlFilter)
            
            cursor = conn.cursor()
            cursor.execute(sql, tuple(kwargs.values()))
            conn.commit()
            return (True, '')
        except Exception as e:
            conn.rollback()
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def delete(tableName, condition):
    with DBMgr.getConnect() as conn:
        try:
            sqlFilter = parseQueryCondition(condition)
            sql = 'delete from %s %s' % (tableName, sqlFilter)
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            return (True, '')
        except Exception as e:
            conn.rollback()
            return (False, str(e))
        finally:
            if cursor:
                cursor.close()

def callProce(procName, *args):
    with DBMgr.getConnect() as conn:
        try:
            cursor = conn.cursor()
            resultArgs = cursor.callproc(procName, args)

            data = [[dict(zip(list(zip(*r._description))[0], x)) for x in r.fetchall()] for r in cursor.stored_results()]
            
            return (True, data[0] if len(data) > 0 else [], resultArgs)
        except Exception as e:
            return (False, str(e), [])
        finally:
            if cursor:
                cursor.close()

def callProceResMore(procName, *args):
    with DBMgr.getConnect() as conn:
        try:
            cursor = conn.cursor()
            resultArgs = cursor.callproc(procName, args)

            data = [[dict(zip(list(zip(*r._description))[0], x)) for x in r.fetchall()] for r in cursor.stored_results()]
            
            return (True, data if len(data) > 0 else [], resultArgs)
        except Exception as e:
            return (False, str(e), [])
        finally:
            if cursor:
                cursor.close()

def execSQL(sql):
    with DBMgr.getConnect() as conn:
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            columns = tuple(x for x in list(zip(*cursor.description))[0])
            data = [dict(zip(columns, x)) for x in cursor.fetchall()]
            total = cursor.rowcount
            
            return (True, total, data)
        except Exception as e:
            return (False, 0, str(e))
        finally:
            if cursor:
                cursor.close()


